package com.ssm.basis.service.office;

import com.ssm.basis.service.util.ReflectionUtil;
import lombok.Getter;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.util.FastByteArrayOutputStream;
import org.springframework.util.ReflectionUtils;
import org.springframework.util.StringUtils;

import javax.validation.constraints.NotBlank;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.List;
import java.util.stream.Collectors;

import static org.springframework.core.annotation.AnnotatedElementUtils.isAnnotated;
import static org.springframework.core.annotation.AnnotationUtils.getAnnotation;

/**
 * https://blog.csdn.net/f641385712/article/details/84067681
 */
public abstract class ExcelUtil {
    public static <T> Workbook createExcel(String title, List<T> data) {
        Excel<T> excel = new Excel<>(title, data);
        excel.writeTitle();
        excel.writeData();
        return excel.getWb();
    }

    public static <T> byte[] createExcelBytes(String title, List<T> data) {
        try (Workbook workbook = createExcel(title, data);
             FastByteArrayOutputStream fbaos = new FastByteArrayOutputStream()) {
            workbook.write(fbaos);
            return fbaos.toByteArray();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return new byte[]{};
    }

    @Getter
    static class Excel<T> {
        Workbook wb;
        Sheet sheet;

        List<T> data;
        String maxTitle;

        // 列属性，用于获取值
        List<Field> fields;
        // 列属性上的注解，用于小标题
        List<ExcelCell> excelCells;

        // 内容样式
        CellStyle cellStyle;
        // 标题样式
        CellStyle titleCellStyle;

        // 记录大小标题行数
        int rowNum;

        public Excel(@NotBlank(message = "大标题不能为空") String title, @NotBlank(message = "数据不能为空") List<T> data) {
            wb = new SXSSFWorkbook();
            this.maxTitle = title;
            this.data = data;

            T t = data.get(0);
            fields = ReflectionUtil.getAllField(t).stream()
                    .filter(x -> isAnnotated(x, ExcelCell.class))
                    .collect(Collectors.toList());
            fields.forEach(ReflectionUtils::makeAccessible);
            excelCells = fields.stream()
                    .map(x -> getAnnotation(x.getAnnotation(ExcelCell.class), ExcelCell.class))
                    .collect(Collectors.toList());

            cellStyle = wb.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION); // 文字水平居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 文字垂直居中
            cellStyle.setBorderBottom(BorderStyle.THIN); // 底边框加黑
            cellStyle.setBorderLeft(BorderStyle.THIN); // 左边框加黑
            cellStyle.setBorderRight(BorderStyle.THIN); // 右边框加黑
            cellStyle.setBorderTop(BorderStyle.THIN); // 上边框加黑
            cellStyle.setWrapText(true); // 单元格内容显示不下时自动换行

            titleCellStyle = wb.createCellStyle();
            titleCellStyle.cloneStyleFrom(cellStyle);
            titleCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 填充单元格
            titleCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.index); // 背景色
            Font font = wb.createFont();
            font.setFontHeightInPoints((short) 12); // 字体大小
            titleCellStyle.setFont(font);
        }

        void writeTitle() {
            sheet = wb.createSheet(); // 主 sheet

            // 大标题不为空，则合并并居中显示
            if (StringUtils.hasLength(maxTitle)) {
                Row row = sheet.createRow(rowNum++); // 创建第一行
                row.setHeightInPoints(30); // 设置行的高度

                Cell cell = row.createCell(0); // 创建第一列
                cell.setCellValue(maxTitle); // 写入数据
                cell.setCellStyle(titleCellStyle); // 为单元格添加背景样式

                if (excelCells.size() > 1) {
                    // 起始行, 终止行, 起始列, 终止列
                    CellRangeAddress cra = new CellRangeAddress(0, 0, 0, excelCells.size() - 1);
                    // 合并单元格显示标题
                    // sheet.addMergedRegion(cra);
                    sheet.addMergedRegionUnsafe(cra);
                    // 使用 RegionUtil 类为合并后的单元格添加边框
                    RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet); // 下边框
                    RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet); // 左边框
                    RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet); // 有边框
                    RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet); // 上边框
                }
            }

            // 填充二级小标题头
            Row row = sheet.createRow(rowNum++); // 创建第二行
            for (int i = 0; i < excelCells.size(); i++) {
                Cell cell = row.createCell(i);
                ExcelCell excelCell = excelCells.get(i);
                cell.setCellValue(excelCell.title());
                cell.setCellStyle(titleCellStyle);
            }

            // 获得总列数
            int coloumNum = sheet.getRow(0).getPhysicalNumberOfCells();
            for (int i = 0; i < coloumNum; i++) {
                sheet.setColumnWidth(i, excelCells.get(i).width()); // 设置列宽
            }
        }

        void writeData() {
            for (int i = 0; i < data.size(); i++) {
                Row row = sheet.createRow(i + rowNum);
                T t = data.get(i);

                for (int j = 0; j < fields.size(); j++) {
                    Cell cell = row.createCell(j);
                    ExcelCell excelCell = excelCells.get(j);
                    Object value = ReflectionUtils.getField(fields.get(j), t);
                    setCellValue(cell, excelCell, value);
                }
            }
        }

        void setCellValue(Cell cell, ExcelCell excelCell, Object value) {
            switch (excelCell.type()) {
                case STRING:
                    cell.setCellValue(String.valueOf(value));
                    break;
                case NUMERIC:
                    cell.setCellValue(Float.parseFloat(String.valueOf(value)));
                    break;
                case BOOLEAN:
                    cell.setCellValue(Boolean.parseBoolean(String.valueOf(value)));
                    break;
                default:
                    cell.setCellValue(String.valueOf(value));
                    break;
            }
            cell.setCellStyle(cellStyle);
        }
    }
}